-- @owner: zou_jialiang050
-- @date: 2024/4/2
-- @testpoint: pip row返回测试

--step1:创建嵌套表类型，其中嵌套嵌套表类型;expect:成功
create or replace type typ_pipelined_0010 as(c1 integer, c2 float(10), c3 text, c4 date, c5 time);
create or replace type tb_type_0010 as table of typ_pipelined_0010;
create or replace type tb_type_0010_1 as table of varchar(100);

--step2:pip row测试;expect:成功
create or replace function func_pipelined_0010(count in number)
returns tb_type_0010 pipelined
as $$
declare
result typ_pipelined_0010;
begin
for i in 1 .. count loop
    RAISE NOTICE 'Number: %', i;
    pipe row(result);
    pipe row(null);
end loop;
return;
end;
$$ language plpgsql;
/

create or replace function func_pipelined_0010_1(count in number)
returns tb_type_0010_1 pipelined
as $$
declare
result typ_pipelined_0010;
begin
for i in 1 .. count loop
    PIPE ROW(i);
    pipe row('');
    pipe row(null);
    RAISE NOTICE 'Number: %', i;
    PIPE ROW('result' || TO_CHAR(i));
    PIPE ROW('result');
    pipe row(1);
end loop;
    pipe row('END');
return;
end;
$$ language plpgsql;
/

--step3:调用pipelined函数并查看返回类型;expect:成功
select func_pipelined_0010(3);
select * from func_pipelined_0010(3);
select * from table(func_pipelined_0010_1(3));
select pg_typeof(func_pipelined_0010(3));

--step4:清理环境;expect:成功
drop function if exists func_pipelined_0010_1;
drop type if exists tb_type_0010 cascade;
drop type if exists type_0010 cascade;
drop type if exists associative_array_0010 cascade;
